package com.uxsino.simo.collector.connections;

import java.io.IOException;
import java.net.URL;
import java.sql.*;
import java.util.*;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.uxsino.simo.connections.AbstractConnection;
import com.uxsino.simo.connections.exception.SimoQueryException;
import com.uxsino.simo.connections.target.JDBCTarget;
import com.uxsino.simo.model.Dataset;
import com.uxsino.simo.model.MultiDataset;

public class JDBCConnection extends AbstractConnection<JDBCTarget> {

    private static Logger logger = LoggerFactory.getLogger(JDBCConnection.class);

    private static Map<String, Map<String, String>> driverConfigs;

    private Connection conn;

    private final Set<Statement> statements = new HashSet<>();

    @Override
    public Object buildCmd(String cmdPattern, Map<String, String> args) {
        return cmdPattern;
    }

    @Override
    public int close() {
        synchronized (statements){
            statements.forEach(s->{
                try {
                    if(s != null && !s.isClosed()){
                        s.close();
                    }
                }catch (Exception e){
                    logger.warn("close statments error: {}", e);
                }
            });
        }

        if (connected && conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
            }
        }
        connected = false;
        super.close();
        return 0;
    }

    @Override
    public int connect(JDBCTarget t) {
        super.connect(t);
        state = 0;
        Class<?> driver = null;
        try {
            String driverName = getDriverName(t);

            if (driverName == null) {
                logger.error("can not find driver for dbType {}", t.type);
                return state;
            }
            driver = Class.forName(driverName);
        } catch (ClassNotFoundException e1) {
            logger.error("failed to load driver:" + getDriverName(t));
            return state;
        }
        String connString = getConnectionString(t);

        try {
            // conn = DriverManager.getConnection(connString, t.getUsername(), t.getPassword());
            java.util.Properties info = new java.util.Properties();
            info.setProperty("user", t.getUsername());
            info.setProperty("password", t.getPassword());
            info.setProperty("allowMultiQueries", "true");
            Enumeration<Driver> des = DriverManager.getDrivers();
            while (des.hasMoreElements()){
                Driver d = des.nextElement();
                if(driver.isInstance(d)){
                    conn = d.connect(connString, info);
                    break;
                }
            }
            //conn = DriverManager.getConnection(connString, info);
            if(conn != null){
                connected = true;
                state = 1;
            }
//            conn = DriverManager.getConnection(connString, info);
//            connected = true;
//            state = 1;
        } catch (SQLException e) {
            logger.error("error connecting to database: " + connString + ". " + "", e);
        }
        return state;
    }

    /**
     * not used for now but maybe some sql statement doesn't work well with execute but needs executeQuery?
     * @param ps
     * @return
     * @throws SQLException
     * @throws SimoQueryException 
     */
    private MultiDataset execDBQuery(PreparedStatement ps) throws SQLException, SimoQueryException {
        MultiDataset mds = new MultiDataset();
        ResultSet rs = ps.executeQuery();

        while (true) {
            try {
                Dataset result = getDataset(rs);
                mds.add(result);
            } finally {
                rs.close();
            }
            if (ps.getMoreResults())
                rs = ps.getResultSet();
            else
                break;
        }
        return mds;
    }

    private MultiDataset execSql(String sql) throws SQLException, SimoQueryException {
        PreparedStatement ps = conn.prepareStatement(sql);
        //ps.closeOnCompletion();
        MultiDataset mds = new MultiDataset();
        synchronized (statements){
            statements.add(ps);
            try {
                ResultSet rs;
                ps.execute();
                while (true) {
                    rs = ps.getResultSet();
                    if (rs != null) {
                        Dataset result = getDataset(rs);
                        mds.add(result);
                    }
                    if (ps.getMoreResults() == false && ps.getUpdateCount() == -1) {
                        break;
                    }
                }

            }finally {
                try {
                    if(!ps.isClosed()){
                        ps.close();
                    }
                    statements.remove(ps);
                }catch (Exception e){
                    logger.warn("close statement error: {}", e);
                }
            }
        }
        return mds;

    }

    @Override
    public Object execCmd(Object cmd) throws SimoQueryException {
        PreparedStatement ps = null;
        MultiDataset mds = null;
        try {
            ps = conn.prepareStatement(cmd.toString());
            ps.setQueryTimeout(60 * 2);// 设置超时时间

            mds = execSql((String) cmd);
        } catch (SQLException e) {
            logger.error("host:{},dbName：{}，执行SQL时候出现异常，错误信息：{}",target.host,target.dbName, e.getMessage());
            throw new SimoQueryException(e);
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    logger.error("host:{},dbName：{}，关闭连接出现异常，错误信息：{}",target.host,target.dbName, e.getMessage());
                }
            }
        }
        return mds;

    }

    //要对这里边的数更改
    private Dataset getDataset(ResultSet rs) throws SimoQueryException {
        Dataset result = new Dataset();
        try {
            ResultSetMetaData rsmd = rs.getMetaData();
            int count = rsmd.getColumnCount();

            for (int i = 1; i <= count; i++) {
                // logger.info("column {} : {}", i, rsmd.getColumnName(i));
                result.columnNames.put(rsmd.getColumnName(i), i);// 第一列不是0，而是1
            }

            /*
            dousil
            字段的类型、字段的名称
             */
            while (rs.next()) {
                String[] row = new String[count];
                for (int i = 0; i < count; i++) {
                   if(2005==rsmd.getColumnType(i+1)){
                       Clob  clob = rs.getClob(rsmd.getColumnName(i+1));
                       if(clob!=null){
                           row[i] = clob.getSubString((long)1,(int)clob.length());
                       }
                   }else{
                       row[i] = rs.getString(i + 1);// 索引从1开始，而不是从0开始
                   }
                }
                result.records.add(row);
            }
        } catch (SQLException e) {
            logger.error("host:{},dbName：{}，执行SQL时候出现异常，错误信息：{}",target.host,target.dbName, e.getMessage());
            throw new SimoQueryException(e);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    logger.error("host:{},dbName：{}，关闭连接出现异常，错误信息：{}",target.host,target.dbName, e.getMessage());
                }
            }
        }
        return result;
    }

    public String getConnectionString(JDBCTarget target) {
        if (target.type.toUpperCase().equals("SQLSERVER")) {
            return getURLScheme(target) + target.host + ":" + target.port + ";" + "databaseName=" + target.dbName;
        }
        if (target.type.toUpperCase().equals("ORACLE")) {
            if (target.connectType != null && target.connectType.equalsIgnoreCase("servername")) {
                return getURLScheme(target) + "//" + target.host + ":" + target.port + "/" + target.dbName;
            } else {
                return getURLScheme(target) + target.host + ":" + target.port + ":" + target.dbName;
            }
        }
        return getURLScheme(target) + target.host + ":" + target.port + "/" + target.dbName;
    }

    public static String getDriverName(JDBCTarget target) {

        if (driverConfigs != null || loadDriverConfigs()) {
            Map<String, String> m = driverConfigs.get(target.type.toUpperCase());
            if (m != null)
                return m.get("driver");
        }
        return null;
    }

    public static String getURLScheme(JDBCTarget target) {
        if (driverConfigs != null /* || loadDriverConfigs() */) {
            Map<String, String> m = driverConfigs.get(target.type.toUpperCase());
            if (m != null)
                return m.get("scheme");
        }
        return null;
    }

    public static boolean loadDriverConfigs() {
        URL configUrl = JDBCConnection.class.getClassLoader().getResource("jdbc-drivers.json");
        ObjectMapper mapper = new ObjectMapper();
        try {
            driverConfigs = mapper.readValue(configUrl, new TypeReference<Map<String, Map<String, String>>>() {
            });
        } catch (IOException e) {
            logger.error("error reading jdbc-drivers.json. " + "", e);
            return false;
        }
        return true;
    }

    static {
        if (driverConfigs == null) {
            loadDriverConfigs();
        }
    }

}
